package com.face.sql.jsu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.naming.java.javaURLContextFactory;

import com.face.bean.jsu.FindPopulation;

public class FindDao {
	
	Connection con;
	ResultSet  rs;
	PreparedStatement pstmt;
	
	/*
	 * 向表格中插入数据
	 * 参数为id和path
	 */
	public void insertData(String id,String path,String province,String city,String location,Timestamp date,String message){
		//java.sql.Date d = new java.sql.Date(date.getTime());
		String sql1 = "SELECT proid from province where province = '"+province+"'";
		String sql2 = "INSERT INTO province(province) VALUES('"+province+"')";
		String sql3 = "SELECT cityid from city where city='"+city+"'";
		String sql4 = "INSERT INTO city(city) VALUES('"+city+"')";
		String sql = "INSERT INTO find_population (faceid,facepath,proid,cityid,location,time,message) VALUES(?,?,?,?,?,?,?)"; 
		ResultSet rs1=null,rs2=null,rs3=null,rs4=null;
		con = JDBCUtil.getConnection();
		try{
			int proid,cityid;
			pstmt = con.prepareStatement(sql1);
			rs1 = pstmt.executeQuery();
			if(rs1.next()){
				proid = rs1.getInt("proid");
			}
			else{
				pstmt = con.prepareStatement(sql2);
				pstmt.execute();
				pstmt = con.prepareStatement(sql1);
				rs2 = pstmt.executeQuery();
				rs2.first();
				proid = rs2.getInt("proid");
			}
			pstmt = con.prepareStatement(sql3);
			rs3 = pstmt.executeQuery();
			if(rs3.next()){
				cityid = rs3.getInt("cityid");
			}
			else{
				pstmt = con.prepareStatement(sql4);
				pstmt.execute();
				pstmt = con.prepareStatement(sql3);
				rs4 = pstmt.executeQuery();
				rs4.first();
				cityid = rs4.getInt("cityid");
			}
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, path);
			pstmt.setInt(3, proid);
			pstmt.setInt(4, cityid);
			pstmt.setString(5, location);
			pstmt.setTimestamp(6, date);
			pstmt.setString(7,message);
			pstmt.executeUpdate();
			System.out.println("插入成功");
		}catch(SQLException e){
			
		}finally{
			try {
				if(rs1!=null)rs1.close();
				if(rs2!=null)rs2.close();
				if(rs3!=null)rs3.close();
				JDBCUtil.CloseConnection(rs4, pstmt, con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
	}
	/*
	 * 通过id唯一标识查询数据
	 */
	public FindPopulation selectByfaceid(String id){
		String sql = "select facepath,province,city,location,time,message "
				+ "from find_population,province,city where "
				+ "province.proid=find_population.proid and "
				+ "city.cityid=find_population.cityid and faceid = ?";
		
		FindPopulation findPopulation = new FindPopulation();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(!rs.next()){
				System.out.println("没有查询到结果");
				return null;
			}else{
				rs.first();
				findPopulation.setFacepath("http://47.106.35.203:8080/find/"+rs.getString("facepath"));
				findPopulation.setProvince(rs.getString("province"));
				findPopulation.setCity(rs.getString("city"));
				findPopulation.setLocation(rs.getString("location"));
				findPopulation.setTimestamp(rs.getTimestamp("time"));
				findPopulation.setMessage(rs.getString("message"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return findPopulation;
	}
	/*
	 * 通过path路径查询数据
	 */
	public String selectByfacepath(String path){
		String sql = "select faceid from find_population where facepath = ?";
		String faceid = "IsNull";
		try {
			    con = JDBCUtil.getConnection();
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, path);
				ResultSet rs = pstmt.executeQuery();
				if(!rs.next()){
					return faceid;
				}else{
					rs.first();
				    faceid = rs.getString(1);
				}
		} catch (SQLException e) {
				e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
			
		return faceid;
	}
	
	/**
	 * 根据省份，时间查询
	 * @param province
	 * @param start
	 * @param end
	 */
	public List<FindPopulation> selectByProvince(String province,String start,String end){
		String sql = "select facepath,province,city,location,time,message from find_population,province,city where"
				+ " province.province=? and province.proid=find_population.proid and city.cityid=find_population.cityid "
				+ " and ?<= DATE_FORMAT(time, '%Y-%m-%d') and ?>= DATE_FORMAT(time, '%Y-%m-%d')";
	
		List<FindPopulation> list = new ArrayList<FindPopulation>();
		
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, province);
			pstmt.setString(2, start);
			pstmt.setString(3, end);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
					FindPopulation findPopulation = new FindPopulation();
					findPopulation.setFacepath(rs.getString("facepath"));
					findPopulation.setProvince(rs.getString("province"));
					findPopulation.setCity(rs.getString("city"));
					findPopulation.setLocation(rs.getString("location"));
					findPopulation.setTimestamp(rs.getTimestamp("time"));
					findPopulation.setMessage(rs.getString("message"));
					list.add(findPopulation);
				}
			    return list;
			
	} catch (SQLException e) {
			e.printStackTrace();
	}finally{
		JDBCUtil.CloseConnection(rs, pstmt, con);
	}
		return list;
	}
	
	public List<FindPopulation> selectByCity(String province,String city,String start,String end){
		String sql = "select facepath,province,city,location,time,message from find_population,province,city where"
				+ " province.province=? and province.proid=find_population.proid"
				+ " and city.city=? and city.cityid=find_population.cityid"
				+ " and ?<= DATE_FORMAT(time, '%Y-%m-%d') and ?>= DATE_FORMAT(time, '%Y-%m-%d')";
	
		
		List<FindPopulation> list = new ArrayList<FindPopulation>();
		
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, province);
			pstmt.setString(2, city);
			pstmt.setString(3, start);
			pstmt.setString(4, end);
			ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					FindPopulation findPopulation = new FindPopulation();
					findPopulation.setFacepath(rs.getString("facepath"));
					findPopulation.setProvince(rs.getString("province"));
					findPopulation.setCity(rs.getString("city"));
					findPopulation.setLocation(rs.getString("location"));
					findPopulation.setTimestamp(rs.getTimestamp("time"));
					findPopulation.setMessage(rs.getString("message"));
					
					list.add(findPopulation);
				}
			    return list;
			
	} catch (SQLException e) {
			e.printStackTrace();
	}finally{
		JDBCUtil.CloseConnection(rs, pstmt, con);
	}
		return list;
	}
	
	public String selectBYcityid(int cityid) {
		String sql = "select city from city where cityid="+cityid;
		String city = new String();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.first();
			city = rs.getString("city");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
		return city;
	}
	
	public void updateByid(String id,String path){
		String sql = "update find_population set facepath = ? where faceid=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, path);
			pstmt.setString(2, id);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功更新"+rtn+"条");
		} catch (SQLException e) {
			System.out.println("更新失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
		
	}
	public void updateBypath(String id,String path){
		String sql = "update find_population set faceid = ? where facepath=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, path);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功更新"+rtn+"条");
		} catch (SQLException e) {
			System.out.println("更新失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	public void deleteByid(String id){
		String sql = "delete from find_population where faceid = ?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功删除"+rtn+"条记录");
		} catch (SQLException e) {
			System.out.println("删除失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	public void deleteBypath(String path){
		String sql = "delete from find_population where facepath = ?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, path);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功删除"+rtn+"条记录");
		} catch (SQLException e) {
			System.out.println("删除失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	/*
	 * 查询所有的数据
	 * 返回一个map容器，里面包含faceid和facepath
	 */
	public Map<String, String> selectAll(){
		Map<String,String> map = new HashMap<String, String>();
		String sql  = "select * from find_population";
		try{
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs  = pstmt.executeQuery();
			while(rs.next()){
				System.out.println("faceid为："+rs.getString("faceid")+"facepath为："+rs.getString("facepath"));
				map.put(rs.getString("faceid"), rs.getString("facepath"));
			}
		}catch(Exception e){
			System.out.println("查询失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return map;
	}
	
}
